package com.rxh.util;

import com.rxh.pojo.merchant.MerchantInfo;
import com.rxh.pojo.sys.SysConstant;
import com.rxh.utils.SystemConstant;
import com.rxh.vo.VoCoreOrder;
import com.rxh.vo.VoCoreOrderChange;
import com.rxh.vo.VoCoreOrderExport;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: 陈俊雄
 * Date: 2018/4/2
 * Time: 9:53
 * Project: Management
 * Package: com.rxh.utils
 */
public class ExcelUtils {

    public static void exportOrderInfoToExcel(HttpServletResponse response, List<VoCoreOrderExport> orderExports, Map<String, Map<String, SysConstant>> constants, MerchantInfo merchantInfo) throws IOException {
        String[] SHEET_TITLES = {"订单信息"};
        String[] ROW_TITLES = {"平台订单号", "商户号", "域名", "商户订单号", "支付方式", "支付类型", "收单账户", "币种",
                "订单金额", "交易汇率", "订单人民币", "结果", "交易时间", "IP", "异常状态", "发货单", "邮箱", "清算金额",
                "清算人民币金额", "清算时间", "清算状态"};
        int cellRangeAddressCount = 0;
        // 系统常量：银行状态
        Map<String, SysConstant> bankStatus = constants.get(SystemConstant.BANK_STATUS);
        // 系统常量：异常状态
        Map<String, SysConstant> exceptionStatus = constants.get(SystemConstant.EXCEPTION_STATUS);
        // 新建excel
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet;
        Row row;
        Cell cell;
        // 字体样式，设置粗体
        Font headerFount = workbook.createFont();
        headerFount.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 单元格样式，设置字体样式
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setFont(headerFount);
        // 单元格样式，格式化时间
        CellStyle cellDateFormat = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cellDateFormat.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        // 数据格式
        DataFormat dataFormat = workbook.createDataFormat();
        // 文本
        CellStyle cellSetString = workbook.createCellStyle();
        cellSetString.setDataFormat(dataFormat.getFormat("@"));
        // 保留两位小数
        CellStyle cellNumFormatTwoDecimalPlaces = workbook.createCellStyle();
        cellNumFormatTwoDecimalPlaces.setDataFormat(dataFormat.getFormat("#,##0.00"));
        // 保留四位小数
        CellStyle cellNumFormatFourDecimalPlaces = workbook.createCellStyle();
        cellNumFormatFourDecimalPlaces.setDataFormat(dataFormat.getFormat("#,####0.0000"));
        // 垂直居中
        CellStyle cellVerticalCenter = workbook.createCellStyle();
        cellVerticalCenter.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        sheet = workbook.createSheet(SHEET_TITLES[0]);
        row = sheet.createRow(0);

        setSheetTitle(ROW_TITLES, sheet, row, headerCellStyle);
        for (int i = 0; i < orderExports.size(); i++) {
            sheet = workbook.getSheetAt(0);
            row = sheet.createRow(i + 1);
            cell = row.createCell(0);
            cell.setCellValue(String.valueOf(orderExports.get(i).getId()));
            cell.setCellStyle(cellSetString);
            cell = row.createCell(1);
            cell.setCellValue("(" + orderExports.get(i).getMerId().toString() + ")" + merchantInfo.getName());
            cell = row.createCell(2);
            cell.setCellValue(orderExports.get(i).getMerchantWebSite().getSiteUrl());
            cell = row.createCell(3);
            cell.setCellValue(orderExports.get(i).getMerOrderId());
            cell.setCellStyle(cellSetString);
            cell = row.createCell(4);
            cell.setCellValue(orderExports.get(i).getPayMode());
            cell = row.createCell(5);
            cell.setCellValue(orderExports.get(i).getPayType());
            cell = row.createCell(6);
            cell.setCellValue(orderExports.get(i).getAcquirerAccount().getName());
            cell = row.createCell(7);
            cell.setCellValue(orderExports.get(i).getCurrency());
            cell = row.createCell(8);
            cell.setCellValue(orderExports.get(i).getAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            cell = row.createCell(9);
            cell.setCellValue(orderExports.get(i).getRate().floatValue());
            cell.setCellStyle(cellNumFormatFourDecimalPlaces);
            cell = row.createCell(10);
            cell.setCellValue(String.valueOf(orderExports.get(i).getRmbAmount()));
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            cell = row.createCell(11);
            cell.setCellValue(bankStatus.get(String.valueOf(orderExports.get(i).getBankStatus())).getName());
            cell = row.createCell(12);
            cell.setCellValue(orderExports.get(i).getTradeTime());
            cell.setCellStyle(cellDateFormat);
            cell = row.createCell(13);
            cell.setCellValue(orderExports.get(i).getOrderDetail().getIp());
            cell = row.createCell(14);
            if (orderExports.get(i).getExceptionStatus() != null) {
                cell.setCellValue(exceptionStatus.get(String.valueOf(orderExports.get(i).getExceptionStatus())).getName());
            } else {
                cell.setCellValue("正常");
            }
            if (orderExports.get(i).getCargoTrack() != null) {
                cell = row.createCell(15);
                cell.setCellValue(orderExports.get(i).getCargoTrack().getExpressNo());
            }
            cell = row.createCell(16);
            cell.setCellValue(orderExports.get(i).getOrderDetail().getEmail());
            cell.setCellStyle(cellSetString);
            cell = row.createCell(17);
            cell.setCellValue(orderExports.get(i).getAmount()
                    .subtract(orderExports.get(i).getMerchantOrder().getDeduct())
                    .subtract(orderExports.get(i).getMerchantOrder().getBond())
                    .floatValue());
            cell.setCellStyle(cellVerticalCenter);
            cellNumFormatTwoDecimalPlaces.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            if (orderExports.get(i).getRmbAmount() != null && orderExports.get(i).getMerchantOrderRmb().getSourceRmbDeduct() != null) {
                cell = row.createCell(18);
                cell.setCellValue(orderExports.get(i).getRmbAmount()
                        .subtract(orderExports.get(i).getMerchantOrderRmb().getSourceRmbDeduct())
                        .subtract(orderExports.get(i).getMerchantOrderRmb().getSourceRmbDeduct())
                        .floatValue());
                cell.setCellStyle(cellVerticalCenter);
                cellNumFormatTwoDecimalPlaces.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
                cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            }
            if (orderExports.get(i).getFinanceStatus() == 1) {
                cell = row.createCell(19);
                cell.setCellValue(orderExports.get(i).getFinanceTime());
                cellSetString.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
                cell.setCellStyle(cellDateFormat);
                cell = row.createCell(20);
                cell.setCellValue("已清算");
                cell.setCellStyle(cellVerticalCenter);
            } else {
                cell = row.createCell(20);
                cell.setCellValue("未清算");
                cell.setCellStyle(cellVerticalCenter);
            }
        }
        workbook.write(response.getOutputStream());
        workbook.close();
    }

    public static void exportBondInfoToExcel(HttpServletResponse response, List<VoCoreOrder> orders, Map<String, SysConstant> bondStatus, MerchantInfo merchantInfo) throws IOException {
        String[] SHEET_TITLES = {"保证金信息"};
        String[] ROW_TITLES =
                {"平台订单号", "商户号", "域名", "商户订单号", "币种", "订单金额", "订单人民币金额", "交易时间",
                        "保证金金额", "保证金人民币金额", "释放时间", "释放状态"};
        // 新建excel
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet;
        Row row;
        Cell cell;
        // 字体样式，设置粗体
        Font headerFount = workbook.createFont();
        headerFount.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 单元格样式，设置字体样式
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setFont(headerFount);
        // 单元格样式，格式化时间
        CellStyle cellDateFormat = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cellDateFormat.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        // 数据格式
        DataFormat dataFormat = workbook.createDataFormat();
        // 文本
        CellStyle cellSetString = workbook.createCellStyle();
        cellSetString.setDataFormat(dataFormat.getFormat("@"));
        // 保留两位小数
        CellStyle cellNumFormatTwoDecimalPlaces = workbook.createCellStyle();
        cellNumFormatTwoDecimalPlaces.setDataFormat(dataFormat.getFormat("#,##0.00"));
        // 保留四位小数
        CellStyle cellNumFormatFourDecimalPlaces = workbook.createCellStyle();
        cellNumFormatFourDecimalPlaces.setDataFormat(dataFormat.getFormat("#,####0.0000"));
        // 垂直居中
        CellStyle cellVerticalCenter = workbook.createCellStyle();
        cellVerticalCenter.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        sheet = workbook.createSheet(SHEET_TITLES[0]);
        row = sheet.createRow(0);
        setSheetTitle(ROW_TITLES, sheet, row, headerCellStyle);
        for (int i = 0; i < orders.size(); i++) {
            sheet = workbook.getSheetAt(0);
            row = sheet.createRow(i + 1);
            cell = row.createCell(0);
            cell.setCellValue(String.valueOf(orders.get(i).getId()));
            cell.setCellStyle(cellSetString);
            cell = row.createCell(1);
            cell.setCellValue("(" + merchantInfo.getId() + ")" + merchantInfo.getName());
            cell = row.createCell(2);
            cell.setCellValue(orders.get(i).getSiteUrl());
            cell = row.createCell(3);
            cell.setCellValue(orders.get(i).getMerOrderId());
            cell = row.createCell(4);
            cell.setCellValue(orders.get(i).getCurrency());
            cell = row.createCell(5);
            cell.setCellValue(orders.get(i).getAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            if (orders.get(i).getRmbAmount() != null) {
                cell = row.createCell(6);
                cell.setCellValue(orders.get(i).getRmbAmount().floatValue());
                cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            }
            cell = row.createCell(7);
            cell.setCellValue(orders.get(i).getTradeTime());
            cell.setCellStyle(cellDateFormat);
            cell = row.createCell(8);
            cell.setCellValue(orders.get(i).getBondAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            if (orders.get(i).getBondRmbAmount() != null) {
                cell = row.createCell(9);
                cell.setCellValue(orders.get(i).getBondRmbAmount().floatValue());
                cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            }
            cell = row.createCell(10);
            if (orders.get(i).getBondTime() != null) {
                cell.setCellValue(orders.get(i).getBondTime());
                cell.setCellStyle(cellDateFormat);
            } else {
                cell.setCellValue("无");
            }
            cell = row.createCell(11);
            cell.setCellValue(bondStatus.get(String.valueOf(orders.get(i).getBondStatus())).getName());
        }
        workbook.write(response.getOutputStream());
        workbook.close();
    }

    public static void exportRefundInfoToExcel(HttpServletResponse response, List<VoCoreOrderChange> orderChangeList, Map<String, Map<String, SysConstant>> constants, MerchantInfo merchantInfo) throws IOException {
        String[] SHEET_TITLES = {"退款信息"};
        String[] ROW_TITLES =
                {"平台订单号", "商户号", "域名", "订单金额", "订单金额CNY", "交易时间", "退款金额", "退款金额CNY",
                        "退款手续费CNY", "退款理由", "退款申请时间", "退款审核时间", "审核结果", "异常状态"};
        Map<String, SysConstant> exceptionStatus = constants.get("ExceptionStatus");
        Map<String, SysConstant> auditedStatus = constants.get("AuditedStatus");
        // 新建excel
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet;
        Row row;
        Cell cell;
        // 字体样式，设置粗体
        Font headerFount = workbook.createFont();
        headerFount.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 单元格样式，设置字体样式
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setFont(headerFount);
        // 单元格样式，格式化时间
        CellStyle cellDateFormat = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cellDateFormat.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        // 数据格式
        DataFormat dataFormat = workbook.createDataFormat();
        // 文本
        CellStyle cellSetString = workbook.createCellStyle();
        cellSetString.setDataFormat(dataFormat.getFormat("@"));
        // 保留两位小数
        CellStyle cellNumFormatTwoDecimalPlaces = workbook.createCellStyle();
        cellNumFormatTwoDecimalPlaces.setDataFormat(dataFormat.getFormat("#,##0.00"));
        // 保留四位小数
        CellStyle cellNumFormatFourDecimalPlaces = workbook.createCellStyle();
        cellNumFormatFourDecimalPlaces.setDataFormat(dataFormat.getFormat("#,####0.0000"));
        // 垂直居中
        CellStyle cellVerticalCenter = workbook.createCellStyle();
        cellVerticalCenter.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        sheet = workbook.createSheet(SHEET_TITLES[0]);
        row = sheet.createRow(0);
        setSheetTitle(ROW_TITLES, sheet, row, headerCellStyle);
        for (int i = 0; i < orderChangeList.size(); i++) {
            sheet = workbook.getSheetAt(0);
            row = sheet.createRow(i + 1);
            cell = row.createCell(0);
            cell.setCellValue(String.valueOf(orderChangeList.get(i).getOrderId()));
            cell.setCellStyle(cellSetString);
            cell = row.createCell(1);
            cell.setCellValue("(" + merchantInfo.getId() + ")" + merchantInfo.getName());
            cell = row.createCell(2);
            cell.setCellValue(orderChangeList.get(i).getSiteUrl());
            cell = row.createCell(3);
            cell.setCellValue(orderChangeList.get(i).getSourceAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            cell = row.createCell(4);
            cell.setCellValue(orderChangeList.get(i).getSourceRmbAmount() == null ? 0f : orderChangeList.get(i).getSourceRmbAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            cell = row.createCell(5);
            cell.setCellValue(orderChangeList.get(i).getTradeTime());
            cell.setCellStyle(cellDateFormat);
            cell = row.createCell(6);
            cell.setCellValue(orderChangeList.get(i).getAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            cell = row.createCell(7);
            cell.setCellValue(orderChangeList.get(i).getRmbAmount() == null ? 0f : orderChangeList.get(i).getRmbAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            cell = row.createCell(8);
            cell.setCellValue(orderChangeList.get(i).getRefundFee().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            cell = row.createCell(9);
            cell.setCellValue(orderChangeList.get(i).getReasons());
            cell = row.createCell(10);
            cell.setCellValue(orderChangeList.get(i).getApplicationTime());
            cell.setCellStyle(cellDateFormat);
            if (orderChangeList.get(i).getAuditTime() != null) {
                cell = row.createCell(11);
                cell.setCellValue(orderChangeList.get(i).getAuditTime());
                cell.setCellStyle(cellDateFormat);
            }
            cell = row.createCell(12);
            cell.setCellValue(auditedStatus.get(orderChangeList.get(i).getStatus().toString()).getName());
            cell = row.createCell(13);
            cell.setCellValue(exceptionStatus.get(orderChangeList.get(i).getType().toString()).getName());
        }
        workbook.write(response.getOutputStream());
        workbook.close();
    }

    public static void exportRefusedInfoToExcel(HttpServletResponse response, List<VoCoreOrderChange> orderChangeList, Map<String, Map<String, SysConstant>> constants, MerchantInfo merchantInfo) throws IOException {
        String[] SHEET_TITLES = {"拒付信息"};
        String[] ROW_TITLES =
                {"平台订单号", "商户号", "域名", "订单金额", "订单金额CNY", "交易时间", "拒付金额", "拒付金额CNY",
                        "拒付处理费CNY", "拒付理由", "拒付申请时间", "拒付审核时间", "审核结果", "异常状态"};
        Map<String, SysConstant> exceptionStatus = constants.get("ExceptionStatus");
        Map<String, SysConstant> auditedStatus = constants.get("AuditedStatus");
        // 新建excel
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet;
        Row row;
        Cell cell;
        // 字体样式，设置粗体
        Font headerFount = workbook.createFont();
        headerFount.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 单元格样式，设置字体样式
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setFont(headerFount);
        // 单元格样式，格式化时间
        CellStyle cellDateFormat = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cellDateFormat.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        // 数据格式
        DataFormat dataFormat = workbook.createDataFormat();
        // 文本
        CellStyle cellSetString = workbook.createCellStyle();
        cellSetString.setDataFormat(dataFormat.getFormat("@"));
        // 保留两位小数
        CellStyle cellNumFormatTwoDecimalPlaces = workbook.createCellStyle();
        cellNumFormatTwoDecimalPlaces.setDataFormat(dataFormat.getFormat("#,##0.00"));
        // 保留四位小数
        CellStyle cellNumFormatFourDecimalPlaces = workbook.createCellStyle();
        cellNumFormatFourDecimalPlaces.setDataFormat(dataFormat.getFormat("#,####0.0000"));
        // 垂直居中
        CellStyle cellVerticalCenter = workbook.createCellStyle();
        cellVerticalCenter.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        sheet = workbook.createSheet(SHEET_TITLES[0]);
        row = sheet.createRow(0);
        setSheetTitle(ROW_TITLES, sheet, row, headerCellStyle);
        for (int i = 0; i < orderChangeList.size(); i++) {
            sheet = workbook.getSheetAt(0);
            row = sheet.createRow(i + 1);
            cell = row.createCell(0);
            cell.setCellValue(String.valueOf(orderChangeList.get(i).getOrderId()));
            cell.setCellStyle(cellSetString);
            cell = row.createCell(1);
            cell.setCellValue("(" + merchantInfo.getId() + ")" + merchantInfo.getName());
            cell = row.createCell(2);
            cell.setCellValue(orderChangeList.get(i).getSiteUrl());
            cell = row.createCell(3);
            cell.setCellValue(orderChangeList.get(i).getSourceAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            cell = row.createCell(4);
            cell.setCellValue(orderChangeList.get(i).getSourceRmbAmount() == null ? 0f : orderChangeList.get(i).getSourceRmbAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            cell = row.createCell(5);
            cell.setCellValue(orderChangeList.get(i).getTradeTime());
            cell.setCellStyle(cellDateFormat);
            cell = row.createCell(6);
            cell.setCellValue(orderChangeList.get(i).getAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            cell = row.createCell(7);
            cell.setCellValue(orderChangeList.get(i).getRmbAmount() == null ? 0f : orderChangeList.get(i).getRmbAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            cell = row.createCell(8);
            cell.setCellValue(orderChangeList.get(i).getDishonor().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            cell = row.createCell(9);
            cell.setCellValue(orderChangeList.get(i).getReasons());
            cell = row.createCell(10);
            cell.setCellValue(orderChangeList.get(i).getApplicationTime());
            cell.setCellStyle(cellDateFormat);
            if (orderChangeList.get(i).getAuditTime() != null) {
                cell = row.createCell(11);
                cell.setCellValue(orderChangeList.get(i).getAuditTime());
                cell.setCellStyle(cellDateFormat);
            }
            cell = row.createCell(12);
            cell.setCellValue(auditedStatus.get(orderChangeList.get(i).getStatus().toString()).getName());
            cell = row.createCell(13);
            cell.setCellValue(exceptionStatus.get(orderChangeList.get(i).getType().toString()).getName());
        }
        workbook.write(response.getOutputStream());
        workbook.close();
    }

    public static void exportLogisticsMgmtToExcel(HttpServletResponse response, List<VoCoreOrderExport> logisticsInfo) throws IOException {

        String[] SHEET_TITLES = {"物流管理"};
        String[] ROW_TITLES = {"平台订单号", "商户订单号", "域名", "交易时间", "状态","邮箱","快递公司","快递单号","查单网址"};
        int cellRangeAddressCount = 0;
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet;
        Row row;
        Cell cell;
        // 字体样式，设置粗体
        Font headerFount = workbook.createFont();
        headerFount.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 单元格样式，设置字体样式
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setFont(headerFount);
        // 单元格样式，格式化时间
        CellStyle cellDateFormat = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cellDateFormat.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        // 数据格式
        DataFormat dataFormat = workbook.createDataFormat();
        // 文本
        CellStyle cellSetString = workbook.createCellStyle();
        cellSetString.setDataFormat(dataFormat.getFormat("@"));
        // 保留两位小数
        CellStyle cellNumFormatTwoDecimalPlaces = workbook.createCellStyle();
        cellNumFormatTwoDecimalPlaces.setDataFormat(dataFormat.getFormat("#,##0.00"));
        // 保留四位小数
        CellStyle cellNumFormatFourDecimalPlaces = workbook.createCellStyle();
        cellNumFormatFourDecimalPlaces.setDataFormat(dataFormat.getFormat("#,####0.0000"));
        // 垂直居中
        CellStyle cellVerticalCenter = workbook.createCellStyle();
        cellVerticalCenter.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        sheet = workbook.createSheet(SHEET_TITLES[0]);
        row = sheet.createRow(0);

        setSheetTitle(ROW_TITLES, sheet, row, headerCellStyle);
        for (int i = 0; i < logisticsInfo.size(); i++) {
            sheet = workbook.getSheetAt(0);
            row = sheet.createRow(i + 1);

            cell = row.createCell(0);
            cell.setCellValue(String.valueOf(logisticsInfo.get(i).getOrder().getId()));

            cell = row.createCell(1);
            cell.setCellValue(logisticsInfo.get(i).getOrder().getMerOrderId());

            cell = row.createCell(2);
            cell.setCellValue(logisticsInfo.get(i).getMerchantWebSite().getSiteUrl());

            cell = row.createCell(3);
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(logisticsInfo.get(i).getOrder().getTradeTime()));
            cell.setCellStyle(cellDateFormat);

            cell = row.createCell(4);
            cell.setCellValue("未发货");

            cell = row.createCell(5);
            cell.setCellValue(logisticsInfo.get(i).getOrderDetail().getEmail());


        }
        workbook.write(response.getOutputStream());
        workbook.close();
    }

    private static void setSheetTitle(String[] ROW_TITLES, Sheet sheet, Row row, CellStyle headerCellStyle) {
        Cell cell;
        for (int i = 0; i < ROW_TITLES.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(ROW_TITLES[i]);
            cell.setCellStyle(headerCellStyle);
            if (i == 0) {
                sheet.setColumnWidth(i, ROW_TITLES[i].getBytes().length * 256 + 1024 * 3);
            } else {
                sheet.setColumnWidth(i, ROW_TITLES[i].getBytes().length * 256 + 1024);
            }
        }
    }
}